package com.game.spacetrader.data;

import java.io.File;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Scanner;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.AsyncTask;
import android.os.Environment;
import android.util.Log;
/**
 * Data.java Handles local db
 * @Version 1.0 October 31,2012
 * @author APatel
 */
public class Data {
	//Database Location
	private final String dbLocation = Environment.getExternalStorageDirectory().getPath() + "/android/com.game.spacetrader/db/";
	//Vars
	private String dbName;
	private File dbFile;
	private SQLiteDatabase database = null;
	private int numColumns = 0;
	private int numRows = 0;
	
	/**
	 * Selects the appropriate database
	 */
	public Data(String dbName){
		this.dbName = dbName;

		try{
			dbFile = new File(dbLocation);
			dbFile.mkdirs();
			dbFile.delete(); //delete the empty db directory (bug with the api)
			database = SQLiteDatabase.openOrCreateDatabase(dbFile+dbName, null);
			database.close();
			database = SQLiteDatabase.openOrCreateDatabase(dbFile+dbName, null);
			database.execSQL("CREATE TABLE IF NOT EXISTS " + dbName.replace(".","") + " (ID INT(3))");
			database.execSQL("DROP TABLE " + dbName.replace(".",""));
			database.execSQL("CREATE TABLE IF NOT EXISTS " + dbName.replace(".","") + " (ID INT(3))");
			database.close();
			//TODO MAKE IT SO IT DOES NOT ALWAYS UPDATE THE DATABASE
			//TODO MAKE INTO ASYNC TASK CLASS WHEN UPDATING	
			//TODO MAKE SO CAN GET # ROWS IN DATABASE
			//dbConnect();
			update();
		} catch(Exception e) {
			Log.d("Data.java","error", e);
		}
	}

	/**
	 * Pulls database, parses data, stores to local db
	 */
	public void update(){
		//TODO CALL SHOULD NOT HAVE ANY PARSING HERE
		FusionClient fc = new FusionClient();
		String raw = fc.updateAF(dbName.replace(".",""));
		String scanData = "";
		Scanner scan = new Scanner(raw);

		while(scan.hasNext() && !scanData.contains("columns"))
			scanData = scan.nextLine();
		scanData = scan.nextLine();
		while(scan.hasNext() && !scanData.contains("rows")){
			if(scanData.length() > 2 && !scanData.contains("]")){
				scanData = scanData.substring(scanData.indexOf("\"")+1, scanData.lastIndexOf("\"")); //remove formatting
				database = SQLiteDatabase.openDatabase(dbFile+dbName, null, Context.MODE_PRIVATE);
				database.execSQL("ALTER TABLE " + dbName.replace(".","") + " ADD COLUMN " + scanData.trim() + " VARCHAR(255)");
				database.close();
			}
			scanData = scan.nextLine();
		}
		scanData = scan.nextLine();
		String tempStr;
		int id = -1;
		while(scan.hasNext()){
			tempStr = "";
			while(!scanData.contains("]")){
				if(scanData.length() > 2 && !scanData.contains("[")){
					tempStr += ", '";
					scanData = scanData.substring(scanData.indexOf("\"")+1, scanData.lastIndexOf("\"")); //remove formatting
					tempStr += scanData + "'";
				}
				scanData = scan.nextLine();
			}
			if(!tempStr.equals("")){
				id+=1;
				database = SQLiteDatabase.openDatabase(dbFile+dbName, null, Context.MODE_PRIVATE);
				database.execSQL("INSERT INTO " + dbName.replace(".","") + " VALUES (" + id+tempStr + ")");
				database.close();
			}
			scanData = scan.nextLine();
		}
		
		database = SQLiteDatabase.openDatabase(dbFile+dbName, null, Context.MODE_PRIVATE);
		Cursor cursor = database.rawQuery("SELECT * FROM "+ dbName.replace(".","") +" WHERE ID=0", null);
		this.numColumns = cursor.getColumnCount();
		Log.d("Data.update", "Num Columns: " + this.numColumns);
		cursor.close();
		database.close();
		
		String[] temp = this.execSQL("SELECT ID FROM @TABLE");
		this.numRows = temp.length;
	}
	
	/**
	 * Linked to DataTest
	 * Returns local SQL data after stored from Fusion
	 * FOR TESTING PURPOSES ONLY!
	 * @return String
	 */
	public String debugView(){
		database = SQLiteDatabase.openDatabase(dbFile+dbName, null, Context.MODE_PRIVATE);		
		Cursor cursor = database.query(dbName.replace(".",""), null, null, null, null, null, null);
		
		int columnCount = cursor.getColumnCount();
		cursor.moveToFirst();
		StringBuilder stringBuilder = new StringBuilder();
		for(int i = 1; i < columnCount; i++){
			stringBuilder.append(cursor.getString(i) + " ");
		}
		stringBuilder.append("\n");
		while(cursor.moveToNext()){
			for(int i = 1; i < columnCount; i++){
				stringBuilder.append(cursor.getString(i) + " ");
			}
			stringBuilder.append("\n");
		}
		cursor.close();
		database.close();
		return stringBuilder.toString();
	}
	
	/**
	 * Linked to DataTest
	 * Returns Raw Fusion Response
	 * FOR TESTING PURPOSES ONLY!
	 * @return String
	 */
	public String debugView2(){
		FusionClient fc = new FusionClient();
		String raw = fc.updateAF(dbName.replace(".",""));
		return raw;
	}
	
	/**
	 * Retrieve data from database 
	 * @param row number
	 * @param column
	 * @return data point
	 */
	public Object getData(int row, String column, char datatype){
		database = SQLiteDatabase.openDatabase(dbFile+dbName, null, Context.MODE_PRIVATE);		
		Cursor cursor = database.rawQuery("SELECT " + column + " FROM " + dbName.replace(".","") + " WHERE ID=" + row, null);
		
		int columnCount = cursor.getColumnCount();
		cursor.moveToFirst();
		StringBuilder stringBuilder = new StringBuilder();
		for(int i = 1; i < columnCount; i++){
			stringBuilder.append(cursor.getString(i) + " ");
		}

		cursor.close();
		database.close();
		String raw = stringBuilder.toString();
		
		Object ret = new Object();
		if(datatype == 'i')
			ret = Integer.parseInt(raw);
		else if(datatype == 'd')
			ret = Double.parseDouble(raw);
		else if(datatype == 's')
			ret = raw;
		return ret;
	}
	

	
	
	/**
	 * Raw SQL command executer
	 * @param command Use "@TABLE" to reference table name
	 * @return results Query results
	 */
	public String[] execSQL(String command){
		command = command.replaceAll("@TABLE", dbName.replace(".",""));
		//Log.d("Data.executeSQL", command);
		database = SQLiteDatabase.openDatabase(dbFile+dbName, null, Context.MODE_PRIVATE);
		Cursor cursor = database.rawQuery(command, null);
		
		ArrayList<String> results = new ArrayList<String>();
		int columnCount = cursor.getColumnCount();
		cursor.moveToFirst();
		StringBuilder stringBuilder = new StringBuilder();
		for(int i = 1; i < columnCount; i++){
			stringBuilder.append(cursor.getString(i) + " ");
		}
		results.add(stringBuilder.toString());
		while(cursor.moveToNext()){
			stringBuilder = new StringBuilder();
			for(int i = 1; i < columnCount; i++){
				stringBuilder.append(cursor.getString(i) + " ");
			}
			results.add(stringBuilder.toString());
		}
		
		cursor.close();
		database.close();
		String[] stringArray = Arrays.copyOf(results.toArray(), results.toArray().length, String[].class);
		return stringArray;
	}
	
	/**
	 * ASYNC ESTABLISH CONN TO DB
	 */
	public void dbConnect(){
		new AsyncConnect().execute();
	}
	
	public int getNumColumns(){
		return this.numColumns;
	}
	
	public int getNumRows(){
		return this.numRows;
	}
}

/**
 * Asynchronously execSQL
 *
 */
class AsyncConnect extends AsyncTask<Void, Void, Void> {
	private String dbName = "universe.db";
	private File dbFile;
	private final String dbLocation = Environment.getExternalStorageDirectory().getPath() + "/android/com.game.spacetrader/db/";
	private SQLiteDatabase database = null;
	@Override
	protected Void doInBackground(Void... conn) {
		dbFile = new File(dbLocation);
		dbFile.mkdirs();
		dbFile.delete(); //delete the empty db directory (bug with the api)
		database = SQLiteDatabase.openOrCreateDatabase(dbFile+dbName, null);
		database.close();
		database = SQLiteDatabase.openOrCreateDatabase(dbFile+dbName, null);
		database.execSQL("CREATE TABLE IF NOT EXISTS " + dbName.replace(".","") + " (ID INT(3))");
		database.execSQL("DROP TABLE " + dbName.replace(".",""));
		database.execSQL("CREATE TABLE IF NOT EXISTS " + dbName.replace(".","") + " (ID INT(3))");
		database.close();
		return null;
	}
	
    protected void onPostExecute(Long result) {
        Log.d("POST","Connection Established");
    }
}
